package com.rickpan.repository;

import com.rickpan.entity.FriendRequest;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

/**
 * 好友申请数据访问层
 */
@Repository
public interface FriendRequestRepository extends JpaRepository<FriendRequest, Long> {

    /**
     * 查找用户收到的好友申请
     */
    Page<FriendRequest> findByToUserIdAndStatus(Long toUserId, FriendRequest.Status status, Pageable pageable);

    /**
     * 查找用户发送的好友申请
     */
    Page<FriendRequest> findByFromUserIdAndStatus(Long fromUserId, FriendRequest.Status status, Pageable pageable);

    /**
     * 查找用户收到的所有好友申请
     */
    List<FriendRequest> findByToUserIdOrderByCreatedAtDesc(Long toUserId);

    /**
     * 查找用户发送的所有好友申请
     */
    List<FriendRequest> findByFromUserIdOrderByCreatedAtDesc(Long fromUserId);

    /**
     * 检查是否存在待处理的好友申请
     */
    Optional<FriendRequest> findByFromUserIdAndToUserIdAndStatus(Long fromUserId, Long toUserId, FriendRequest.Status status);

    /**
     * 检查两个用户之间是否存在任何好友申请（双向检查）
     */
    @Query("SELECT fr FROM FriendRequest fr WHERE " +
           "((fr.fromUserId = :userId1 AND fr.toUserId = :userId2) OR " +
           "(fr.fromUserId = :userId2 AND fr.toUserId = :userId1)) AND " +
           "fr.status = :status")
    List<FriendRequest> findBidirectionalRequests(@Param("userId1") Long userId1, 
                                                  @Param("userId2") Long userId2, 
                                                  @Param("status") FriendRequest.Status status);

    /**
     * 统计用户收到的待处理申请数量
     */
    long countByToUserIdAndStatus(Long toUserId, FriendRequest.Status status);

    /**
     * 统计用户发送的待处理申请数量
     */
    long countByFromUserIdAndStatus(Long fromUserId, FriendRequest.Status status);

    /**
     * 查找过期的待处理申请
     */
    @Query("SELECT fr FROM FriendRequest fr WHERE fr.status = 'PENDING' AND fr.expiresAt < :now")
    List<FriendRequest> findExpiredPendingRequests(@Param("now") LocalDateTime now);

    /**
     * 批量更新过期申请状态
     */
    @Modifying
    @Transactional
    @Query("UPDATE FriendRequest fr SET fr.status = 'EXPIRED', fr.updatedAt = :now " +
           "WHERE fr.status = 'PENDING' AND fr.expiresAt < :now")
    int markExpiredRequests(@Param("now") LocalDateTime now);

    /**
     * 删除用户的所有好友申请
     */
    void deleteByFromUserIdOrToUserId(Long fromUserId, Long toUserId);

    /**
     * 查找用户最近的好友申请活动
     */
    @Query("SELECT fr FROM FriendRequest fr WHERE " +
           "(fr.fromUserId = :userId OR fr.toUserId = :userId) " +
           "ORDER BY fr.updatedAt DESC")
    List<FriendRequest> findRecentActivityByUserId(@Param("userId") Long userId, Pageable pageable);

    /**
     * 获取用户的好友申请统计
     */
    @Query("SELECT fr.status, COUNT(fr.status) FROM FriendRequest fr " +
           "WHERE fr.toUserId = :userId " +
           "GROUP BY fr.status")
    List<Object[]> getReceivedRequestsStats(@Param("userId") Long userId);

    /**
     * 获取用户发送的好友申请统计
     */
    @Query("SELECT fr.status, COUNT(fr.status) FROM FriendRequest fr " +
           "WHERE fr.fromUserId = :userId " +
           "GROUP BY fr.status")
    List<Object[]> getSentRequestsStats(@Param("userId") Long userId);

    /**
     * 检查是否可以发送好友申请（防止重复申请）
     */
    @Query("SELECT CASE WHEN COUNT(fr) > 0 THEN false ELSE true END FROM FriendRequest fr " +
           "WHERE fr.fromUserId = :fromUserId AND fr.toUserId = :toUserId AND " +
           "fr.status IN ('PENDING', 'ACCEPTED')")
    boolean canSendFriendRequest(@Param("fromUserId") Long fromUserId, @Param("toUserId") Long toUserId);

    /**
     * 查找即将过期的申请（用于提醒）
     */
    @Query("SELECT fr FROM FriendRequest fr WHERE " +
           "fr.status = 'PENDING' AND " +
           "fr.expiresAt BETWEEN :now AND :reminderTime")
    List<FriendRequest> findRequestsNearExpiry(@Param("now") LocalDateTime now, 
                                              @Param("reminderTime") LocalDateTime reminderTime);

    /**
     * 根据申请ID和接收者ID查找申请（用于权限验证）
     */
    Optional<FriendRequest> findByIdAndToUserId(Long id, Long toUserId);

    /**
     * 根据申请ID和发送者ID查找申请（用于权限验证）
     */
    Optional<FriendRequest> findByIdAndFromUserId(Long id, Long fromUserId);
}
